<?php
/**
 * Notes: excel上传
 * author: chen
 * DateTime: 2021/12/27 15:58
 * @package app\Https\RMS\Controllers
 */

namespace Xtsb\Cims\Excel;

use Xtsb\Cims\Error\ApiErrorDesc;
use Xtsb\Cims\Exception\ApiException;
use Xtsb\Cims\Route\Route;
use Xtsb\Cims\SiteSetup\Excel\ExcelFields;
use think\facade\Db;

class ExcelFactory
{
  //横向单元格标识
  const CEll_NAME = array(
    'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
    'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ',
    'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ',
    'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ',
    'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ', 'DR', 'DS', 'DT', 'DU', 'DV', 'DW', 'DX', 'DY', 'DZ'
  );

  /**
   * Notes:
   * author: chen
   * DateTime: 2022/3/30 20:56
   * @param array $keyArr 数据key值
   * @param int[] $unsetRowNums 移除的行下标
   * @param boolean $keyData 返回key对应的data值
   * @param array $options 配置
   *        int sheet=0  工作表下标
   *        string dirname=>''    图片保存目录
   *        bool formula=false     是否读取公式
   * @return array
   */
  public static function upload($keyArr = [], $unsetRowNums = [1], $keyData = true, $options = null)
  {
    $file = request()->file('files');
    $dirname = request()->param('dirname', 'excel'); //文件保存目录

    //仅上传excel表格
    if (!in_array($file->getOriginalExtension(), ['xls', 'xlsx'])) {
      throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, '只支持导入Excel文件');
    }
    try {
//      $files[] = $file;
//      //验证文件类型
//      validate(['excel' => 'fileSize:102400000|fileExt:xlx,xlsx'])->check($files);

      $savename = \think\facade\Filesystem::disk('public')->putFile($dirname, $file,
        function () use ($file) {
          $fileName = md5(str_replace('.' . $file->getOriginalExtension(), '', $file->getOriginalName()));
          return $fileName;
        });

    } catch (\think\exception\ValidateException $e) {
      dd($e->getMessage());
    }


    $excelData = Sheet::importExcel('./storage/' . $savename, 0, $options);
    @unlink('./storage/' . $savename);//删除文件

    $format = [];
    if (empty($keyArr)) {
      return $excelData;
    } elseif (is_string($keyArr)) {
//      $excelFields = self::excelFields($keyArr);
      $excelFields = ExcelFields::excelFields($keyArr);
      if (!$excelFields) {
        return null;
      }
      $keyArr = $excelFields['keys'];
      $format = $excelFields['format'];
      $multiple = $excelFields['multiple'];
      $labels = $excelFields['label'] ?? [];
      //验证表格模板是否正确
      if (!empty($unsetRowNums)) {
        foreach ($unsetRowNums as $unsetRowNum) {
          if (isset($excelData[$unsetRowNum]) && !empty(array_diff(array_values($excelData[$unsetRowNum]), $labels))) {
            throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, '请使用正确的Excel模板导入');
          }
        }
      }
    } else {

    }


    $data = [];
    if ($keyData) {
      $i = 0;
      foreach ($excelData as $key => $item) {
        if (in_array($key, $unsetRowNums)) {
          unset($excelData[$key]);//移出指定行
          continue;
        }
        foreach ($keyArr as $k => $keyName) {
          $data[$i][$keyName] = !empty($item[self::CEll_NAME[$k]]) ? trim($item[self::CEll_NAME[$k]]) : null;
          if (isset($multiple[$k]) && $multiple[$k]) {
            //数组格式  切割字符串成数据
            $cellFormat = $format[$k];//单元格 数据格式
            $data[$i][$keyName] = array_map(function ($item) use ($cellFormat) {
              if (isset($cellFormat)) {
                switch ($cellFormat) {
                  case 'date':
                    $item = self::excelTime($item);
                    break;
                  default:
                    break;
                }
              }
              return $item;
            }, explode('；', str_replace(';', '；', $data[$i][$keyName])));

          } else {
            //非数组格式 格式化处理
            if (isset($format[$k])) {
              switch ($format[$k]) {
                case 'date':
                  $data[$i][$keyName] = self::excelTime($data[$i][$keyName]);
                  break;
                default:
                  break;
              }
            }
          }
        }
        $i++;
      }
      unset($excelData, $excelFields, $keyArr, $format, $multiple);

    } else {

      $data = $excelData;
    }

    return $data;
  }


  public static function export($data, $url = null)
  {
    if ($url) {
      $data['data'] = ExcelFields::exportData($url, $data['data']);
    }

    $respon = Sheet::exportExcel($data['data'], $data['filename'], isset($data['freeze_pane']) ? $data['freeze_pane'] : 'A2');

    unset($data['data']);
    $result['file'] = $respon;
    $result['filename'] = $data['filename'];
    $result['mime_type'] = 'xlsx';
    unset($respon);
    return $result;
//    return 3333;
  }

  public static function exportMore($data)
  {

    $respon = Sheet::exportExcelMore($data['data'], $data['filename'], isset($data['freeze_pane']) ? $data['freeze_pane'] : 'A2');

    $result['file'] = $respon;
    $result['filename'] = $data['filename'];
    $result['mime_type'] = 'xlsx';
    unset($respon);
    return $result;
//    return 3333;
  }


  /**
   * @param $excelExportUrl string 导出接口 动态路由
   * @return void|array
   */
//  public static function excelFields($excelExportUrl)
//  {
//    if ($excelExportUrl) {
//      $excelExportUrl = str_replace(['/xtsb/', 'xtsb/'], '', $excelExportUrl);
//
//      $where['cuid'] = CUID;
////      $where['uuid'] = UUID;
//      $where['name'] = $excelExportUrl;
//      $where['type'] = 1;//1-excel配置
//      $setupInfo = Db::name('user_site_setup')->field('content')->json(['content'])->where($where)->find();
//
//      //获取原始数据
//      $urls = explode('/', $excelExportUrl);
//      $excelControllerName = $urls[0];//导出方法所在控制器名称
//      $excelActionName = $urls[1];//导出方法所在控制器操作方法名称
//      $serverName = 'app\\Common\\Excel\\Fields\\' . Route::getController($excelControllerName);//驼峰法转化
//      $list = $serverName::$excelActionName();
//      //读取原数据配置信息
//      $excelConfig = [];
//      foreach ($list as $item) {
//        $excelConfig[$item['value']] = $item;
//      }
//
//      $keys = [];//字段名称
//      $labels = [];//标题
//      $format = [];//数据格式
//      $multiple = [];//是否多个数据
//      if ($setupInfo) {
//        foreach ($setupInfo['content'] as $item) {
//          $keys[] = $item;
//          $labels[] = $excelConfig[$item]['label'] ?? null;
//          $format[] = $excelConfig[$item]['format'] ?? null;
//          $multiple[] = $excelConfig[$item]['multiple'] ?? false;
//        }
//        return [
//          'keys' => $keys,
//          'label' => $labels,
//          'format' => $format,
//          'multiple' => $multiple,
//        ];
//      } else {
//
//        if ($list) {
//          foreach ($list as $item) {
//            $keys[] = $item['value'];
//            $labels[] = $item['label'];
//            $format[] = $item['format'] ?? null;
//            $multiple[] = $item['multiple'] ?? false;
//          }
//          return [
//            'keys' => $keys,
//            'label' => $labels,
//            'format' => $format,
//            'multiple' => $multiple,
//          ];
//        }
//      }
//    }
//    return null;
//  }


  /**
   * 格式化excel时间
   * @param $time
   * @return void|string
   */
  private static function excelTime($time)
  {
    //验证是否时间字符串
    $arr = explode('-', $time);
    if (count($arr) == 3 && strlen($arr[0]) == 4 && strlen($arr[1]) == 2) {

      if (checkdate($arr[1], $arr[2], $arr[0])) {
        return $time;
      } else {
//        throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, '时间格式错误');
        return null;
      }
    }


    if (is_numeric($time)) {
      $time = (float)$time;
      if (strpos($time, '.') !== false) {
        $timeFormat = 'Y-m-d H:i:s';
      } else {
        $timeFormat = 'Y-m-d';
      }
      //excel 格式时间戳 转化成php格式 时间戳
      $time = date($timeFormat, \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($time, 'Asia/Shanghai'));
//      $time = date('Y-m-d H:i:s', \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($time, 'Asia/Shanghai'));
//      $time = date($timeFormat, ($time - 25569) * 86400);
      return $time;
    }

    return null;
  }

}
